﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data;

namespace DQ.Helper.Office
{
    public partial class ExcelHelper : IDisposable
    {
        private ExcelHelper(HSSFWorkbook hssfWb, ISheet sheet)
        {
            _HSSFWorkbook = hssfWb;
            _CurrSheet = sheet;
        }
        private HSSFWorkbook _HSSFWorkbook;
        private ISheet _CurrSheet;
        /// <summary>
        /// 当前Sheet的位置（-1表示当前Sheet为NULL）
        /// </summary>
        private int CurrentSheetIndex
        {
            get
            {
                if (CurrSheet != null)
                {
                    return _HSSFWorkbook.ActiveSheetIndex;
                }
                else
                {
                    return -1;
                }
            }
        }

        private ISheet CurrSheet
        {
            get { return _CurrSheet; }
            set { _CurrSheet = value; }
        }
        public int SheetsCount
        {
            get { return _HSSFWorkbook.NumberOfSheets; }

        }

        /// <summary>
        /// 合并单元格，合并后单元格的赋值要从开始行，开始列（合并后的单元格占用的空间不变）
        /// </summary>
        /// <param name="startRowIndex">开始的行（从0开始）</param>
        /// <param name="endRowIndex">终止的行</param>
        /// <param name="startColIndex">开始的列（从0开始）</param>
        /// <param name="endColIndex">终止的列</param>
        /// <returns></returns>
        public int MergeCells(int startRowIndex, int endRowIndex, int startColIndex, int endColIndex)
        {
            int result = CurrSheet.AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, startColIndex, endColIndex));
            return result;

        }
        /// <summary>
        /// 创建Sheet，并切换到该Sheet
        /// </summary>
        /// <param name="sheetName"></param>
        public void CreateSheet(string sheetName)
        {
            _HSSFWorkbook.CreateSheet(sheetName);
        }
        public void CloneSheet()
        {
            var sheetIndex = CurrentSheetIndex;
            CloneSheet(sheetIndex);
        }
        /// <summary>
        /// 切换到指定的Sheet、
        /// </summary>
        /// <param name="sheetIndex"></param>
        public void SwitchToSheet(int sheetIndex)
        {
            CurrSheet = _HSSFWorkbook.GetSheetAt(sheetIndex);
        }
        /// <summary>
        /// 删除Sheet
        /// </summary>
        /// <param name="sheetIndex"></param>
        public void RemoveSheet(int sheetIndex)
        {
            _HSSFWorkbook.RemoveSheetAt(sheetIndex);
        }

        public void ChangeCurrentSheetName(string sheetName)
        {
            ChangeSheetName(CurrentSheetIndex, sheetName);
        }
        public void ChangeSheetName(int sheetIndex, string sheetName)
        {
            _HSSFWorkbook.SetSheetName(sheetIndex, sheetName);
        }

        /// <summary>
        /// 克隆Sheet，并切换到该Sheet
        /// </summary>
        public void CloneSheet(int sheetIndex)
        {
            CurrSheet = _HSSFWorkbook.CloneSheet(sheetIndex);
        }
        public void SetCellVal(int rowIndex, int colIndex, string val)
        {
            int row = rowIndex;
            int column = colIndex;
            if (row >= 0 && column >= 0)
            {
                IRow rowCell = CurrSheet.GetRow(row);
                if (rowCell == null)
                {
                    rowCell = CurrSheet.CreateRow(row);
                }
                ICell cellCur = rowCell.GetCell(column);
                if (cellCur == null)
                {
                    cellCur = rowCell.CreateCell(column);
                }
                cellCur.SetCellValue(val);
            }
        }

        public string GetCellVal(int rowIndex, int colIndex)
        {
            IRow row = CurrSheet.GetRow(rowIndex);
            ICell cell = row.GetCell(colIndex);
            return cell.StringCellValue;
        }
        /// <summary>
        /// 保存操作到文件（可重复保存）
        /// </summary>
        /// <param name="filePath"></param>
        public void Save(string filePath)
        {
            using (var stream = File.OpenWrite(filePath))
            {
                _HSSFWorkbook.Write(stream);
            }
        }

        public void Save(Stream stream)
        {
            _HSSFWorkbook.Write(stream);
        }


        #region 私有方法

        #endregion


        public void Dispose()
        {
            throw new NotImplementedException();
        }
    }

    public partial class ExcelHelper
    {
        /// <summary>
        /// 新建一个Excel文档，并建立一个默认Sheet
        /// </summary>
        /// <returns></returns>
        public static ExcelHelper Create()
        {
            return Create("");
        }
        /// <summary>
        /// 新建一个Excel文档，并建立一个Sheet
        /// </summary>
        /// <param name="sheetName">可为空</param>
        /// <returns></returns>
        public static ExcelHelper Create(string sheetName)
        {
            HSSFWorkbook wk = new HSSFWorkbook();
            ISheet sheet = null;
            if (string.IsNullOrEmpty(sheetName))
            {
                sheet = wk.CreateSheet();
            }
            else
            {
                sheet = wk.CreateSheet(sheetName);
            }
            return new ExcelHelper(wk, sheet);
        }

        public static ExcelHelper Open(string fileFullPath, string sheetName)
        {
            using (FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.ReadWrite))
            {
                return Open(fs, sheetName);
            }

        }
        public static ExcelHelper Open(Stream stream, string sheetName)
        {
            HSSFWorkbook wk = new HSSFWorkbook(stream);
            ISheet sheet = null;
            if (string.IsNullOrEmpty(sheetName))
            {
                sheet = wk.GetSheetAt(0);
            }
            else
            {
                sheet = wk.GetSheet(sheetName);
            }
            return new ExcelHelper(wk, sheet);
        }
        public static ExcelHelper Open(Stream stream)
        {
            return Open(stream, "");
        }
        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
        {
            MemoryStream ms = new MemoryStream();
            using (var excelOpt = ExcelHelper.Create(sheetName))
            {
                int rowIndex = 0;
                foreach (DataColumn column in sourceTable.Columns)
                {
                    excelOpt.SetCellVal(rowIndex, column.Ordinal, column.ColumnName);

                    rowIndex++;
                }

                // handling value.
                foreach (DataRow row in sourceTable.Rows)
                {
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        var val = row[column];
                        if (val != null)
                        {
                            excelOpt.SetCellVal(rowIndex, column.Ordinal, val.ToString());
                        }

                    }
                    rowIndex++;
                }
                excelOpt.Save(ms);
                ms.Position = 0;

            }

            return ms;
        }


        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
        {
            using (excelFileStream)
            using (var excelOpt = ExcelHelper.Open(excelFileStream))
            {
                DataSet ds = new DataSet();
                for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
                {
                    excelOpt.SwitchToSheet(a);
                    DataTable table = new DataTable();

                    HSSFRow headerRow = sheet.GetRow(headerRowIndex);
                    int cellCount = headerRow.LastCellNum;
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                        {
                            // 如果遇到第一个空列，则不再继续向后读取
                            cellCount = i + 1;
                            break;
                        }

                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row = sheet.GetRow(i);
                        if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
                        {
                            // 如果遇到第一个空行，则不再继续向后读取
                            break;
                        }

                        DataRow dataRow = table.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }

                        table.Rows.Add(dataRow);
                    }
                    ds.Tables.Add(table);
                }

                return ds;
            }



        }
    }
}
